﻿using Md.Api.Entity.ModelDB;
using MySqlX.XDevAPI.Common;
using PetaPoco.NetCore;

namespace Md.Petapoco.Repositories.MdTrainingEducation;
/// <summary>
/// 系统用户仓储
/// </summary>
public class SysUserRep : BaseRep<SysUserModel>
{
    /// <summary>
    /// 用户登录
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="userPwd">用户密码</param>
    /// <returns>SysUserModel</returns>
    public SysUserModel Login(string? userName, string? userPwd)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select user_id
                ,dept_id
                ,user_name
                ,nick_name
                ,user_type
                ,email
                ,phonenumber
                ,sex
                ,avatar
                ,status
                ,del_flag
                ,login_ip
                ,login_date
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from sys_user
            where 1 = 1 and user_name = @0 and password=@1
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<SysUserModel>(sqlStr, userName, userPwd);
    }
    /// <summary>
    /// 通过用户编号获取系统用户信息
    /// </summary>
    /// <param name="userId">用户编号</param>
    /// <returns>SysUserModel</returns>
    public SysUserModel GetSysUserByUserId(long userId)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select user_id
                ,dept_id
                ,user_name
                ,nick_name
                ,user_type
                ,email
                ,phonenumber
                ,sex
                ,avatar
                ,status
                ,del_flag
                ,login_ip
                ,login_date
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from sys_user
            where 1 = 1 and user_id = @0
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<SysUserModel>(sqlStr, userId);
    }
    /// <summary>
    /// 通过用户编号获取系统用户密码
    /// </summary>
    /// <param name="userId">用户编号</param>
    /// <returns>SysUserModel</returns>
    public SysUserModel GetSysUserPwdByUserId(long userId)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select password,del_flag,status
            from sys_user
            where 1 = 1 and user_id = @0 limit 1
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<SysUserModel>(sqlStr, userId);
    }
    /// <summary>
    /// 获取系统用户列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>IEnumerable<SysUserModel></returns>
    public IEnumerable<SysUserModel> GetSysUserList(SysUserModel model, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.UserId > 0)
        {
            sqlWhere += " and user_id = @0 ";
        }
        if (model.DeptId > 0)
        {
            sqlWhere += " and dept_id = @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserName))
        {
            sqlWhere += " and user_name like @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.NickName))
        {
            sqlWhere += " and nick_name like @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserType))
        {
            sqlWhere += " and user_type in (@4) ";
        }
        if (!string.IsNullOrWhiteSpace(model.Email))
        {
            sqlWhere += " and email like @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Phonenumber))
        {
            sqlWhere += " and phonenumber like @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Sex))
        {
            sqlWhere += " and sex = @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and del_flag = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @10 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @11 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @12 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @13 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select user_id
                ,dept_id
                ,user_name
                ,nick_name
                ,user_type
                ,email
                ,phonenumber
                ,sex
                ,avatar
                ,status
                ,del_flag
                ,login_ip
                ,login_date
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from sys_user
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<SysUserModel>(
            sqlStr,
            model.UserId,
            model.DeptId,
            string.Format(@"%{0}%", model.UserName),
            string.Format(@"%{0}%", model.NickName),
            model.UserType,
            string.Format(@"%{0}%", model.Email),
            string.Format(@"%{0}%", model.Phonenumber),
            model.Sex,
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 分页获取系统用户列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="pageIndex">当前页</param>
    /// <param name="pageSize">每页条数</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>Page<SysUserModel></returns>
    public Page<SysUserModel> GetSysUserPageList(SysUserModel model, int pageIndex, int pageSize, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.UserId > 0)
        {
            sqlWhere += " and user_id = @0 ";
        }
        if (model.DeptId > 0)
        {
            sqlWhere += " and dept_id = @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserName))
        {
            sqlWhere += " and user_name like @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.NickName))
        {
            sqlWhere += " and nick_name like @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserType))
        {
            sqlWhere += " and user_type in (@4) ";
        }
        if (!string.IsNullOrWhiteSpace(model.Email))
        {
            sqlWhere += " and email like @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Phonenumber))
        {
            sqlWhere += " and phonenumber like @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Sex))
        {
            sqlWhere += " and sex = @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and del_flag = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @10 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @11 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @12 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @13 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select user_id
                ,dept_id
                ,user_name
                ,nick_name
                ,user_type
                ,email
                ,phonenumber
                ,sex
                ,avatar
                ,status
                ,del_flag
                ,login_ip
                ,login_date
                ,create_by
                ,create_time
                ,update_by
                ,update_time
                ,remark
            from sys_user
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Page<SysUserModel>(
            pageIndex,
            pageSize,
            sqlStr,
            model.UserId,
            model.DeptId,
            string.Format(@"%{0}%", model.UserName),
            string.Format(@"%{0}%", model.NickName),
            model.UserType,
            string.Format(@"%{0}%", model.Email),
            string.Format(@"%{0}%", model.Phonenumber),
            model.Sex,
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 通过用户编号更新系统用户
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>long</returns>
    public long UpdateSysUserByUserId(SysUserModel model)
    {
        var sql = new Sql();

        sql.Append(" update `sys_user` set ");

        #region ===set===
        if (model.DeptId > 0)
        {
            sql.Append(" dept_id = @0, ", model.DeptId);
        }

        if (!string.IsNullOrWhiteSpace(model.UserName))
        {
            sql.Append(" user_name = @0, ", model.UserName);
        }

        if (!string.IsNullOrWhiteSpace(model.NickName))
        {
            sql.Append(" nick_name = @0, ", model.NickName);
        }

        if (!string.IsNullOrWhiteSpace(model.UserType))
        {
            sql.Append(" user_type = @0, ", model.UserType);
        }

        if (!string.IsNullOrWhiteSpace(model.Email))
        {
            sql.Append(" email = @0, ", model.Email);
        }

        if (!string.IsNullOrWhiteSpace(model.Phonenumber))
        {
            sql.Append(" phonenumber = @0, ", model.Phonenumber);
        }

        if (!string.IsNullOrWhiteSpace(model.Sex))
        {
            sql.Append(" sex = @0, ", model.Sex);
        }

        if (!string.IsNullOrWhiteSpace(model.Avatar))
        {
            sql.Append(" avatar = @0, ", model.Avatar);
        }

        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sql.Append(" status = @0, ", model.Status);
        }

        if (!string.IsNullOrWhiteSpace(model.Remark))
        {
            sql.Append(" remark = @0, ", model.Remark);
        }

        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sql.Append(" update_by = @0, ", model.UpdateBy);
        }

        sql.Append(" update_time = sysdate() ");
        #endregion

        sql.Append(" where 1 = 1 and del_flag = @0 and user_Id = @1 ", model.DelFlag, model.UserId);

        return MdTrainingEducationDB.GetInstance().Execute(sql);

    }
    /// <summary>
    /// 通过用户编号更新系统用户密码
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>long</returns>
    public long UpdateSysUserPwdByUserId(long userId,string userPwd)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
                set password = @1
                where 1 = 1 and user_Id = @0
         ");
        #endregion

        var res = MdTrainingEducationDB.GetInstance().Update<SysUserModel>(
            sqlStr,
            userId,
            userPwd
        );

        return res;
    }
}